USE [PGMOBILE]
GO

/****** Object:  StoredProcedure [dbo].[ObtenerDemorasHistoricas]    Script Date: 01/27/2010 03:12:24 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO



-- =============================================
-- Author:		Grupo PGMobile 
-- Create date: <Create Date,,>
-- Description:	Obtiene el historico de demoras de la posicion actual de un bondi a la parada
-- =============================================
ALTER PROCEDURE [dbo].[ObtenerDemorasHistoricas] 
	-- Add the parameters for the stored procedure here
	@LatitudBondi float = 477930.78125, 
	@LongitudBondi float = 6140172.5,
	@LatitudParada float = 477844.40625, 
	@LongitudParada float = 6140124.5,
	@Diff float = 0.0005,
	@DiaMin int = 6,
	@DiaMax int = 0,
	@HoraMax time = '20:00:00',
	@HoraMin time = '18:00:00',
	@Ascendente bit = 1,
	@IdLinea int = 22
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	SELECT TOP 10
      DATEADD(N, DATEDIFF(N, po.[PO_HORA], 0), po2.[PO_HORA]) as diff
	  FROM [PGMOBILE].[dbo].[POSICION_OMNIBUS] po
	  JOIN [PGMOBILE].[dbo].[POSICION_OMNIBUS] po2 on po.OMNIBUS_ID = po2.OMNIBUS_ID
	  where 
	  po.PO_LATITUD < (@LatitudBondi + @Diff)
	  and po.PO_LATITUD > (@LatitudBondi - @Diff)
	  and po.PO_LONGITUD < (@LongitudBondi + @Diff)
	  and po.PO_LONGITUD > (@LongitudBondi - @Diff)
	  and ((@DiaMin = @DiaMax
	  and po.PO_DIA = @DiaMin
	  and po.PO_HORA <= @HoraMax
	  and po.PO_HORA >= @HoraMin)
	  or
	  (
		  (po.PO_DIA = @DiaMin and po.PO_HORA >= @HoraMin)
		  or
		  (po.PO_DIA = @DiaMax and po.PO_HORA <= @HoraMax)
	  )
	  )
	  and po.PO_ASCENDENTE = @Ascendente
	  and po.LINEA_ID = @IdLinea
	  and po2.PO_ASCENDENTE = @Ascendente
	  and po2.PO_LATITUD < (@LatitudParada + @Diff)
	  and po2.PO_LATITUD > (@LatitudParada - @Diff)
	  and po2.PO_LONGITUD < (@LongitudParada + @Diff)
	  and po2.PO_LONGITUD > (@LongitudParada - @Diff)
	  and (
	  (po2.PO_FECHA = po.PO_FECHA
	  and po2.PO_HORA > po.PO_HORA)
	  or (@DiaMin <> @DiaMax 
	  and po2.PO_FECHA = DATEADD(day, 1, po.PO_FECHA))
	  )
	  and po2.LINEA_ID = @IdLinea
	  and po2.OMNIBUS_ID = po.OMNIBUS_ID
	  and po2.ID_VUELTA = po.ID_VUELTA
END



GO


